package com.zkn.component.office.excel.handler;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import com.zkn.component.office.excel.template.ExcelTemplate;
import com.zkn.component.office.excel.template.SheetTemplate;
import com.zkn.component.office.excel.utils.ExcelUtils;

public class ExcelTemplateHandler {

	private ExcelTemplate excelTemplate;
	private Workbook templateWorkbook;
	private Workbook outputWorkbook;
	private Map<String, SheetTemplate> sheetTemplates;
	
	public ExcelTemplateHandler(ExcelTemplate excelTemplate) {
		this.excelTemplate = excelTemplate;
	}

	public void handle() throws Exception {
		
		validateTemplate();
		
		readExcelTemplate();
		
		writeData2Excel();
		
		outputExcel();
		
		release();
	}
	
	private void writeData2Excel() throws Exception {
		for (Object sheetData : excelTemplate.getSheetDataList()) {
			writeData2Sheet(sheetData);
		}
	}
	
	private void writeData2Sheet(Object sheetData) throws Exception {
		String sheetName = ExcelFieldHandler.parseSheetName(sheetData.getClass());
		Sheet sheet = outputWorkbook.getSheet(sheetName);
		replishSheetData(sheet, sheetData);
	}
	
	private void replishSheetData(Sheet sheet, Object sheetData) throws Exception {
		
		replishExcelField(sheet, sheetData);
		
		replishExcelDataList(sheet, sheetData);
	}

	private void replishExcelDataList(Sheet sheet, Object sheetData) throws Exception {
		List<List<?>> dataList = ExcelDataListHandler.parseExcelDataList(sheetData);
		
		if(dataList == null || dataList.isEmpty()){
			return ;
		}
		
		for(List<?> list : dataList){
			
			for(int i = 0; i < list.size(); i++){
				replishExcelDataListField(sheet, list.get(i), i, list.size()-1);
			}
		}
	}

	private void replishExcelDataListField(Sheet sheet, Object data, int addRowCount, int shiftrow) throws Exception {
		Map<String, Cell> cellMap = sheetTemplates.get(sheet.getSheetName()).getCellMap();
		Map<String, Object> map = ExcelFieldHandler.handle(data);
		
		Row row = null;
		String key = (String) map.keySet().toArray()[0];
		Cell cell = cellMap.get(key);
		if(addRowCount > 0){
			row = sheet.createRow(cell.getRowIndex()+addRowCount);
		} else if(addRowCount == 0){
			sheet.shiftRows(cell.getRowIndex()+1, sheet.getLastRowNum(), shiftrow);
			row = sheet.getRow(cell.getRowIndex());
		}
		
		for(Entry<String, Object> en : map.entrySet()){
			cell = cellMap.get(en.getKey());
			Cell oCell = sheet.getRow(cell.getRowIndex()+addRowCount).getCell(cell.getColumnIndex());
			Cell styleCell = sheet.getRow(cell.getRowIndex()).getCell(cell.getColumnIndex());
			if(oCell == null){
				oCell = row.createCell(cell.getColumnIndex());
			}
			
			oCell.setCellStyle(styleCell.getCellStyle());
			fillBackgroundColor(oCell);
			
			ExcelUtils.setCellValue(oCell, en.getValue());
		}
	}

	private void fillBackgroundColor(Cell cell) {
		Short bgColor = null;
		if(cell.getRowIndex() % 2 == 0){
			bgColor = excelTemplate.getEvenLineBackgroundColor();
		} else {
			bgColor = excelTemplate.getOddLineBackgroundColor();
		}
		
		if(bgColor == null){
			return ;
		}
		
//		cell.getCellStyle().setFillBackgroundColor(bgColor);
		cell.getCellStyle().setFillForegroundColor(bgColor);
	}

	private void replishExcelField(Sheet sheet, Object sheetData) throws Exception {
		Map<String, Cell> cellMap = sheetTemplates.get(sheet.getSheetName()).getCellMap();
		Map<String,Object> map = ExcelFieldHandler.handle(sheetData);
		for(Entry<String, Cell> en : cellMap.entrySet()){
			Cell cell = en.getValue();
			Cell oCell = sheet.getRow(cell.getRowIndex()).getCell(cell.getColumnIndex());
			ExcelUtils.setCellValue(oCell, map.get(en.getKey()));
		}
	}

	private void validateTemplate() throws Exception {
		if(StringUtils.isBlank(excelTemplate.getExcelTemplateFilepath())){
			throw new Exception("ExcelTemplateFilepath is required.");
		}
		if(StringUtils.isBlank(excelTemplate.getExcelOutputFilepath())){
			throw new Exception("ExcelOutputFilepath is required.");
		}
		if(excelTemplate.getSheetDataList().isEmpty()){
			throw new Exception("No data need to write.");
		}
	}
	
	private void readExcelTemplate() throws Exception{
		templateWorkbook = WorkbookFactory.create(new FileInputStream(excelTemplate.getExcelTemplateFilepath()));
		outputWorkbook = WorkbookFactory.create(new FileInputStream(excelTemplate.getExcelTemplateFilepath()));
		
		sheetTemplates = new LinkedHashMap<String, SheetTemplate>();
		for(int a = 0; a < templateWorkbook.getNumberOfSheets(); a++){
			Sheet sheet = templateWorkbook.getSheetAt(a);
            sheetTemplates.put(sheet.getSheetName(), buildSheetTemplate(a, sheet));
		}
	}

	private SheetTemplate buildSheetTemplate(int index, Sheet sheet) {
		SheetTemplate sheetTemplate = new SheetTemplate();
		sheetTemplate.setSheetName(sheet.getSheetName());
		sheetTemplate.setSheetIndex(index);
		sheetTemplate.setCellMap(readCellStype(sheet));
		return sheetTemplate;
	}

	private Map<String, Cell> readCellStype(Sheet sheet) {
		Map<String,Cell> map = new HashMap<String, Cell>();
		for(int i = 0; i <= sheet.getLastRowNum(); i++){
            Row row = sheet.getRow(i);
            for (Cell cell : row) {
				if(cell.getStringCellValue().startsWith("$")){
					map.put(cell.getStringCellValue(), cell);
				}
			}
       }
		return map;
	}

	private void outputExcel() throws Exception {
		outputWorkbook.write(new FileOutputStream(excelTemplate.getExcelOutputFilepath()));
	}

	private void release() throws Exception {
		if(templateWorkbook != null){
			templateWorkbook.close();
		}
		if(outputWorkbook != null){
			outputWorkbook.close();
		}
	}
}
